import json
import mysql.connector
from datetime import datetime
import uuid

def connect_to_database():
    # 数据库连接配置
    config = {
        'user': 'root',
        'password': '520777',
        'host': 'localhost',
        'database': 'earth_radio',
        'raise_on_warnings': True
    }
    
    try:
        return mysql.connector.connect(**config)
    except mysql.connector.Error as err:
        print(f"Error connecting to database: {err}")
        return None

def insert_station_data(cursor, station):
    # 准备INSERT语句
    insert_query = """
    INSERT INTO radio_stations (
        changeuuid, stationuuid, serveruuid, name, url, url_resolved, homepage,
        favicon, tags, country, countrycode, iso_3166_2, state, language,
        languagecodes, votes, lastchangetime, lastchangetime_iso8601,
        codec, bitrate, hls, lastcheckok, lastchecktime, lastchecktime_iso8601,
        lastcheckoktime, lastcheckoktime_iso8601, lastlocalchecktime,
        lastlocalchecktime_iso8601, clicktimestamp, clicktimestamp_iso8601,
        clickcount, clicktrend, ssl_error, geo_lat, geo_long, geo_distance,
        has_extended_info
    ) VALUES (
        %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,
        %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,
        %s, %s, %s, %s, %s, %s
    )
    """
    
    # 处理时间格式
    def parse_datetime(dt_str):
        if not dt_str:
            return None
        try:
            return datetime.fromisoformat(dt_str.replace('Z', '+00:00'))
        except:
            return None

    # 处理标签列表
    tags = ','.join(station.get('tags', [])) if station.get('tags') else None
    
    # 准备数据
    data = (
        str(uuid.uuid4()),  # changeuuid
        station.get('stationuuid'),
        station.get('serveruuid'),
        station.get('name'),
        station.get('url'),
        station.get('url_resolved'),
        station.get('homepage'),
        station.get('favicon'),
        tags,
        station.get('country'),
        station.get('countrycode'),
        station.get('iso_3166_2', ''),
        station.get('state'),
        station.get('language'),
        station.get('languagecodes'),
        station.get('votes', 0),
        parse_datetime(station.get('lastchangetime')),
        parse_datetime(station.get('lastchangetime_iso8601')),
        station.get('codec'),
        station.get('bitrate'),
        station.get('hls', 0),
        station.get('lastcheckok', 1),
        parse_datetime(station.get('lastchecktime')),
        parse_datetime(station.get('lastchecktime_iso8601')),
        parse_datetime(station.get('lastcheckoktime')),
        parse_datetime(station.get('lastcheckoktime_iso8601')),
        parse_datetime(station.get('lastlocalchecktime')),
        parse_datetime(station.get('lastlocalchecktime_iso8601')),
        parse_datetime(station.get('clicktimestamp')),
        parse_datetime(station.get('clicktimestamp_iso8601')),
        station.get('clickcount', 0),
        station.get('clicktrend'),
        station.get('ssl_error', 0),
        station.get('geo_lat'),
        station.get('geo_long'),
        station.get('geo_distance'),
        station.get('has_extended_info', 0)
    )
    
    try:
        cursor.execute(insert_query, data)
        return True
    except mysql.connector.Error as err:
        print(f"Error inserting station {station.get('name')}: {err}")
        return False

def main():
    # 连接数据库
    connection = connect_to_database()
    if not connection:
        return
    
    cursor = connection.cursor()
    
    try:
        # 读取JSON文件
        with open('stations.json', 'r', encoding='utf-8') as file:
            stations = json.load(file)
        
        # 计数器
        success_count = 0
        error_count = 0
        
        # 逐条插入数据
        for station in stations:
            if insert_station_data(cursor, station):
                success_count += 1
            else:
                error_count += 1
                
            # 每1000条提交一次
            if (success_count + error_count) % 1000 == 0:
                connection.commit()
                print(f"Processed {success_count + error_count} records...")
        
        # 最后提交剩余的事务
        connection.commit()
        
        print(f"\nImport completed:")
        print(f"Successfully imported: {success_count} records")
        print(f"Failed to import: {error_count} records")
        
    except Exception as e:
        print(f"Error processing file: {e}")
        connection.rollback()
    
    finally:
        cursor.close()
        connection.close()

if __name__ == "__main__":
    main()